package org.jeecgframework.web.service.impl;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.persistence.Table;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.model.json.ComboTree;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.model.json.Highchart;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.util.BrowserUtils;
import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.IpUtil;
import org.jeecgframework.core.util.MyBeanUtils;
import org.jeecgframework.core.util.PropertiesUtil;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.core.util.oConvertUtils;
import org.jeecgframework.tag.vo.easyui.ComboTreeModel;
import org.jeecgframework.web.service.ZyFileLogServiceI;
import org.jeecgframework.web.service.ZyInspectionReportServiceI;
import org.jeecgframework.web.system.pojo.base.TSDepart;
import org.jeecgframework.web.system.pojo.base.TSLog;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.pojo.base.ZyEquipmentTypeEntity;
import org.jeecgframework.web.system.pojo.base.ZyFileLogEntity;
import org.jeecgframework.web.system.pojo.base.ZyInspectionPlanEntity;
import org.jeecgframework.web.system.pojo.base.ZyInspectionReportEntity;
import org.jeecgframework.web.system.pojo.base.ZyPlanRecordEntity;
import org.jeecgframework.web.system.pojo.base.ZyReportAttachmentEntity;
import org.jeecgframework.web.system.pojo.base.ZySnCheckRecordEntity;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import antlr.StringUtils;

@Service("zyInspectionReportService")
@Transactional
public class ZyInspectionReportServiceImpl extends CommonServiceImpl implements ZyInspectionReportServiceI {

	@Autowired
	private ZyFileLogServiceI zyFileLogService;
	@Autowired
	private SystemService systemService;
	private List<ZyReportAttachmentEntity> pushList;
	
 	public <T> void delete(T entity) {
 		super.delete(entity);
 		//执行删除操作配置的sql增强
		this.doDelSql((ZyInspectionReportEntity)entity);
 	}
 	
 	public <T> Serializable save(T entity) {
 		Serializable t = super.save(entity);
 		//执行新增操作配置的sql增强
 		this.doAddSql((ZyInspectionReportEntity)entity);
 		return t;
 	}
 	
 	public <T> void saveOrUpdate(T entity) {
 		super.saveOrUpdate(entity);
 		//执行更新操作配置的sql增强
 		this.doUpdateSql((ZyInspectionReportEntity)entity);
 	}
 	
 	/**
	 * 默认按钮-sql增强-新增操作
	 * @param id
	 * @return
	 */
 	public boolean doAddSql(ZyInspectionReportEntity t){
	 	return true;
 	}
 	/**
	 * 默认按钮-sql增强-更新操作
	 * @param id
	 * @return
	 */
 	public boolean doUpdateSql(ZyInspectionReportEntity t){
	 	return true;
 	}
 	/**
	 * 默认按钮-sql增强-删除操作
	 * @param id
	 * @return
	 */
 	public boolean doDelSql(ZyInspectionReportEntity t){
	 	return true;
 	}
 	
 	/**
	 * 替换sql中的变量
	 * @param sql
	 * @return
	 */
 	public String replaceVal(String sql,ZyInspectionReportEntity t){
 		sql  = sql.replace("#{id}",String.valueOf(t.getId()));
 		sql  = sql.replace("#{cm_id}",String.valueOf(t.getCmId()));
 		sql  = sql.replace("#{cm_actual_name}",String.valueOf(t.getCmActualName()));
 		sql  = sql.replace("#{cm_actual_addr}",String.valueOf(t.getCmActualAddr()));
 		sql  = sql.replace("#{inp_contacter_id}",String.valueOf(t.getInpContacterId()));
 		sql  = sql.replace("#{inp_con_user}",String.valueOf(t.getInpConUser()));
 		sql  = sql.replace("#{inp_con_user_tel}",String.valueOf(t.getInpConUserTel()));
 		sql  = sql.replace("#{inp_con_user_email}",String.valueOf(t.getInpConUserEmail()));
 		sql  = sql.replace("#{houseName}",String.valueOf(t.getHouseName()));
 		sql  = sql.replace("#{tms_brand}",String.valueOf(t.getTmsBrand()));
 		sql  = sql.replace("#{server_brand}",String.valueOf(t.getServerBrand()));
 		sql  = sql.replace("#{server_model}",String.valueOf(t.getServerModel()));
 		sql  = sql.replace("#{inp_begin_time}",String.valueOf(t.getInpBeginTime()));
 		sql  = sql.replace("#{inp_end_time}",String.valueOf(t.getInpEndTime()));
 		sql  = sql.replace("#{yt_score_rm}",String.valueOf(t.getYtScoreRm()));
 		sql  = sql.replace("#{yt_rm_temperature}",String.valueOf(t.getYtRmTemperature()));
 		sql  = sql.replace("#{yt_rm_humidity}",String.valueOf(t.getYtRmHumidity()));
 		sql  = sql.replace("#{yt_score_screen}",String.valueOf(t.getYtScoreScreen()));
 		sql  = sql.replace("#{yt_screen_gain}",String.valueOf(t.getYtScreenGain()));
 		sql  = sql.replace("#{yt_services_life}",String.valueOf(t.getYtServicesLife()));
 		sql  = sql.replace("#{yt_scope_w}",String.valueOf(t.getYtScopeW()));
 		sql  = sql.replace("#{yt_scope_h}",String.valueOf(t.getYtScopeH()));
 		sql  = sql.replace("#{yt_pd}",String.valueOf(t.getYtPd()));
 		sql  = sql.replace("#{yt_score_ce}",String.valueOf(t.getYtScoreCe()));
 		sql  = sql.replace("#{yt_problem}",String.valueOf(t.getYtProblem()));
 		sql  = sql.replace("#{qj_score_overall}",String.valueOf(t.getQjScoreOverall()));
 		sql  = sql.replace("#{qj_overall_cl_mp}",String.valueOf(t.getQjOverallClMp()));
 		sql  = sql.replace("#{qj_score_dust_filter}",String.valueOf(t.getQjScoreDustFilter()));
 		sql  = sql.replace("#{qj_dust_filter_cl_mp}",String.valueOf(t.getQjDustFilterClMp()));
 		sql  = sql.replace("#{qj_score_aire_inlet}",String.valueOf(t.getQjScoreAireInlet()));
 		sql  = sql.replace("#{qj_air_inlet_cl_mp}",String.valueOf(t.getQjAirInletClMp()));
 		sql  = sql.replace("#{qj_score_ele_box}",String.valueOf(t.getQjScoreEleBox()));
 		sql  = sql.replace("#{qj_ele_box_cl_mp}",String.valueOf(t.getQjEleBoxClMp()));
 		sql  = sql.replace("#{gx_score_cam_lens}",String.valueOf(t.getGxScoreCamLens()));
 		sql  = sql.replace("#{gx_cam_lens_cl_mp}",String.valueOf(t.getGxCamLensClMp()));
 		sql  = sql.replace("#{gx_cam_lens}",String.valueOf(t.getGxCamLens()));
 		sql  = sql.replace("#{gx_score_reflective_lens}",String.valueOf(t.getGxScoreReflectiveLens()));
 		sql  = sql.replace("#{gx_reflective_lens_cl_mp}",String.valueOf(t.getGxReflectiveLensClMp()));
 		sql  = sql.replace("#{gx_score_light_path}",String.valueOf(t.getGxScoreLightPath()));
 		sql  = sql.replace("#{gx_light_path}",String.valueOf(t.getGxLightPath()));
 		sql  = sql.replace("#{gx_light_path_charges_mp}",String.valueOf(t.getGxLightPathChargesMp()));
 		sql  = sql.replace("#{lq_score_vent_speed}",String.valueOf(t.getLqScoreVentSpeed()));
 		sql  = sql.replace("#{lq_vent_speed}",String.valueOf(t.getLqVentSpeed()));
 		sql  = sql.replace("#{lq_score_coolant}",String.valueOf(t.getLqScoreCoolant()));
 		sql  = sql.replace("#{lq_coolant_h}",String.valueOf(t.getLqCoolantH()));
 		sql  = sql.replace("#{lq_coolant_mp}",String.valueOf(t.getLqCoolantMp()));
 		sql  = sql.replace("#{xd_score_xn}",String.valueOf(t.getXdScoreXn()));
 		sql  = sql.replace("#{xd_xn_box_status_mp}",String.valueOf(t.getXdXnBoxStatusMp()));
 		sql  = sql.replace("#{xd_ele}",String.valueOf(t.getXdEle()));
 		sql  = sql.replace("#{xd_power}",String.valueOf(t.getXdPower()));
 		sql  = sql.replace("#{xd_brand}",String.valueOf(t.getXdBrand()));
 		sql  = sql.replace("#{xd_run_time}",String.valueOf(t.getXdRunTime()));
 		sql  = sql.replace("#{xd_run_count}",String.valueOf(t.getXdRunCount()));
 		sql  = sql.replace("#{xd_max_life}",String.valueOf(t.getXdMaxLife()));
 		sql  = sql.replace("#{xd_min_life}",String.valueOf(t.getXdMinLife()));
 		sql  = sql.replace("#{xd_score_joints}",String.valueOf(t.getXdScoreJoints()));
 		sql  = sql.replace("#{xd_joints}",String.valueOf(t.getXdJoints()));
 		sql  = sql.replace("#{total_run_time}",String.valueOf(t.getTotalRunTime()));
 		sql  = sql.replace("#{is_system_calibration_time}",String.valueOf(t.getIsSystemCalibrationTime()));
 		sql  = sql.replace("#{xl_score_ac}",String.valueOf(t.getXlScoreAc()));
 		sql  = sql.replace("#{xl_ac_into_line_mp}",String.valueOf(t.getXlAcIntoLineMp()));
 		sql  = sql.replace("#{xl_score_voltage}",String.valueOf(t.getXlScoreVoltage()));
 		sql  = sql.replace("#{xl_voltage_type}",String.valueOf(t.getXlVoltageType()));
 		sql  = sql.replace("#{xl_voltage}",String.valueOf(t.getXlVoltage()));
 		sql  = sql.replace("#{xl_zero_voltage}",String.valueOf(t.getXlZeroVoltage()));
 		sql  = sql.replace("#{yx_score_focus}",String.valueOf(t.getYxScoreFocus()));
 		sql  = sql.replace("#{yx_focus_state}",String.valueOf(t.getYxFocusState()));
 		sql  = sql.replace("#{yx_score_frame}",String.valueOf(t.getYxScoreFrame()));
 		sql  = sql.replace("#{yx_screen_frame_state}",String.valueOf(t.getYxScreenFrameState()));
 		sql  = sql.replace("#{yx_light_c}",String.valueOf(t.getYxLightC()));
 		sql  = sql.replace("#{yx_light_left}",String.valueOf(t.getYxLightLeft()));
 		sql  = sql.replace("#{yx_light_center}",String.valueOf(t.getYxLightCenter()));
 		sql  = sql.replace("#{yx_light_right}",String.valueOf(t.getYxLightRight()));
 		sql  = sql.replace("#{yx_color_state}",String.valueOf(t.getYxColorState()));
 		sql  = sql.replace("#{yx_score_3d}",String.valueOf(t.getYxScore3d()));
 		sql  = sql.replace("#{yx_3d_type}",String.valueOf(t.getYx3dType()));
 		sql  = sql.replace("#{yx_3d_light}",String.valueOf(t.getYx3dLight()));
 		sql  = sql.replace("#{rj_barco_dp}",String.valueOf(t.getRjBarcoDp()));
 		sql  = sql.replace("#{rj_dctp}",String.valueOf(t.getRjDctp()));
 		sql  = sql.replace("#{rj_barcor_dp_udt}",String.valueOf(t.getRjBarcorDpUdt()));
 		sql  = sql.replace("#{rj_dctp_udt}",String.valueOf(t.getRjDctpUdt()));
 		sql  = sql.replace("#{rj_ti}",String.valueOf(t.getRjTi()));
 		sql  = sql.replace("#{rj_icp}",String.valueOf(t.getRjIcp()));
 		sql  = sql.replace("#{rj_ims1000}",String.valueOf(t.getRjIms1000()));
 		sql  = sql.replace("#{rj_ti_udt}",String.valueOf(t.getRjTiUdt()));
 		sql  = sql.replace("#{rj_icp_udt}",String.valueOf(t.getRjIcpUdt()));
 		sql  = sql.replace("#{rj_ims1000_udt}",String.valueOf(t.getRjIms1000Udt()));
 		sql  = sql.replace("#{rj_icmp}",String.valueOf(t.getRjIcmp()));
 		sql  = sql.replace("#{rj_tms}",String.valueOf(t.getRjTms()));
 		sql  = sql.replace("#{rj_1}",String.valueOf(t.getRj1()));
 		sql  = sql.replace("#{rj_2}",String.valueOf(t.getRj2()));
 		sql  = sql.replace("#{rj_icmptms_mp}",String.valueOf(t.getRjIcmptmsMp()));
 		sql  = sql.replace("#{rj_noc_software_udt}",String.valueOf(t.getRjNocSoftwareUdt()));
 		sql  = sql.replace("#{rj_noc_brand_udt}",String.valueOf(t.getRjNocBrandUdt()));
 		sql  = sql.replace("#{rj_noc_is_online}",String.valueOf(t.getRjNocIsOnline()));
 		sql  = sql.replace("#{rj_pcf_test_mp}",String.valueOf(t.getRjPcfTestMp()));
 		sql  = sql.replace("#{og_mp}",String.valueOf(t.getOgMp()));
 		sql  = sql.replace("#{score_projector_mb}",String.valueOf(t.getScoreProjectorMb()));
 		sql  = sql.replace("#{projector_mb_mp}",String.valueOf(t.getProjectorMbMp()));
 		sql  = sql.replace("#{before_score}",String.valueOf(t.getBeforeScore()));
 		sql  = sql.replace("#{after_score}",String.valueOf(t.getAfterScore()));
 		sql  = sql.replace("#{remark}",String.valueOf(t.getRemark()));
 		sql  = sql.replace("#{training_num}",String.valueOf(t.getTrainingNum()));
 		sql  = sql.replace("#{training_score}",String.valueOf(t.getTrainingScore()));
 		sql  = sql.replace("#{training_point}",String.valueOf(t.getTrainingPoint()));
 		sql  = sql.replace("#{comprehensive_evaluation}",String.valueOf(t.getComprehensiveEvaluation()));
 		sql  = sql.replace("#{UUID}",UUID.randomUUID().toString());
 		return sql;
 	}

	public void saveAttachment(String reportId, String[] attArr,String attachType) {
		List<ZyReportAttachmentEntity> IrAttachments = super.findHql("FROM ZyReportAttachmentEntity WHERE reportId = ? AND type = ?", reportId,attachType);
		if(attArr != null && attArr.length > 0){
			List<ZyReportAttachmentEntity> ras = new ArrayList<ZyReportAttachmentEntity>();
			for (String saveFileLogId : attArr) {
				boolean flag = true;
				for (ZyReportAttachmentEntity zyIrAttachment : IrAttachments) {
					if(zyIrAttachment.getFileLogId().equals(saveFileLogId)){
						flag = false;
						break;
					}
				}
				if(flag){
					ZyReportAttachmentEntity ra = new ZyReportAttachmentEntity();
					ra.setFileLogId(saveFileLogId);
					ra.setReportId(reportId);
					ra.setType(attachType);
					ras.add(ra);
				}
			}
			super.batchSave(ras);
		}
	}
	
	@Override
	public List<Map<String, Object>> findReportAttachment(String id,String attachType) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append("	ra.report_id AS reportId, ra.type, ");
		sql.append("	fl.id AS fileKey, fl.old_file_name AS oldFileName");
		sql.append(" FROM ");
		sql.append("	zy_report_attachment ra ");
		sql.append(" LEFT JOIN zy_file_log fl ON fl.id = ra.file_log_id ");
		sql.append(" WHERE ");
		sql.append("	report_id = '").append(id).append("' ");
		sql.append("	AND type = '").append(attachType).append("' ");
		return super.findForJdbc(sql.toString());
	}

	@Override
	public void deleteAttach(String irId, String fileBeanId, String type) {
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE ");
		sql.append(" FROM ");
		sql.append(" zy_report_attachment ");
		sql.append(" WHERE ");
		sql.append(" report_id = '").append(irId).append("' ");
		sql.append(" AND file_log_id = '").append(fileBeanId).append("' ");
		sql.append(" AND type = '").append(type).append("' ");
		super.executeSql(sql.toString());
		zyFileLogService.deleteFileLogAndFile(fileBeanId);
	}

	@Override
	public List<Map<String, Object>> getIpProblemReportData(String colum,Object value,String year,String departIds, String esTypes) {
		List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	COUNT(distinct re.report_id) AS total,re.`month` ");
		sql.append(" FROM ");
		sql.append(" 	zy_report_exceptions re");
		sql.append(" LEFT JOIN zy_inspection_report ir on ir.id = re.report_id ");
		sql.append(" LEFT JOIN zy_inspection_plan ip on ip.id = ir.inspection_plan_id ");
		sql.append(" LEFT JOIN zy_equipment_spare es on es.id = ip.equipment_spare_id ");
		sql.append(" WHERE ");
		sql.append(" 	re.`year` = '").append(year).append("' ");
		if(StringUtil.isNotEmpty(colum)){
			sql.append(" AND ").append(colum).append(" = '").append(value).append("' ");
		}
		if(!departIds.equals("")){
			String[] departIdsArr = departIds.split(",");
			sql.append(" AND es.belong_org_id IN(");
			for (int i = 0; i < departIdsArr.length; i++) {
				if(i == departIdsArr.length - 1){
					sql.append("'").append(departIdsArr[i]).append("'");
				}else{
					sql.append("'").append(departIdsArr[i]).append("',");
				}
			}
			sql.append(") ");
		}
		if(!esTypes.equals("")){
			String[] types = esTypes.split(",");
			sql.append(" AND es.type IN(");
			for (int i = 0; i < types.length; i++) {
				if(i == types.length - 1){
					sql.append("'").append(types[i]).append("'");
				}else{
					sql.append("'").append(types[i]).append("',");
				}
			}
			sql.append(") ");
		}
		sql.append(" GROUP BY ");
		sql.append(" 	`month` ");
		sql.append(" ORDER BY ");
		sql.append(" 	`month` ");
		//捋顺 list中的map顺序，按照 month排序，如果没有对应month 那么 给设置 0
		List<Map<String, Object>> mapList = super.findForJdbc(sql.toString());
		for (int i = 1; i < 13; i++) {
			boolean hadData = false;
			for (Map<String, Object> map : mapList) {
				if(map != null && map.get("month").toString().equals(String.valueOf(i))){
					hadData = true;
					rsMapList.add(map);
					break;
				}
			}
			if(!hadData){
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("total", 0);
				map.put("month", i);
				rsMapList.add(map);
			}
		}
		return rsMapList;
	}
	
	@Override
	public Map<String, Object> getTotalPlansByMonth(String year,String departIds, String esType) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("    COUNT(1) AS total, ");
		sql.append("    MONTH (ip.report_commit_time) AS _month ");
		sql.append(" FROM ");
		sql.append("    zy_inspection_plan ip ");
		sql.append(" LEFT JOIN zy_equipment_spare es on es.id = ip.equipment_spare_id ");
		sql.append(" WHERE ");
		sql.append("    YEAR (ip.ip_plan_date) = '").append(year).append("' ");
		if(!departIds.equals("")){
			String[] departIdsArr = departIds.split(",");
			sql.append(" AND es.belong_org_id in(");
			for (int i = 0; i < departIdsArr.length; i++) {
				if(i == departIdsArr.length - 1){
					sql.append("'").append(departIdsArr[i]).append("'");
				}else{
					sql.append("'").append(departIdsArr[i]).append("',");
				}
			}
			sql.append(") ");
		}
		if(!esType.equals("")){
			String[] types = esType.split(",");
			sql.append(" AND es.type IN(");
			for (int i = 0; i < types.length; i++) {
				if(i == types.length - 1){
					sql.append("'").append(types[i]).append("'");
				}else{
					sql.append("'").append(types[i]).append("',");
				}
			}
			sql.append(") ");
		}
		sql.append("    AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
		sql.append(" GROUP BY ");
		sql.append("    _month ");
		List<Map<String, Object>> reMapList = super.findForJdbc(sql.toString());
		Map<String,Object> monthTotalMap = new HashMap<String, Object>();
		Integer total = 0;
		/*for (Map<String, Object> map : reMapList) {
			monthTotalMap.put(map.get("month").toString(), map.get("total").toString());
		}*/
		for(int i = 1 ;i < 13 ;i++){
			boolean hadRecord = false;
			for(Map<String, Object> map : reMapList){
				if(i == Integer.valueOf(map.get("_month").toString())){
					monthTotalMap.put(i+"", map.get("total").toString());
					total += Integer.valueOf(map.get("total").toString());
					hadRecord = true;
					break;
				}
			}
			if(!hadRecord){
				monthTotalMap.put(i+"", 0);
			}
		}
		monthTotalMap.put("yearTotal", total);
		
		sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	COUNT(distinct re.report_id) AS total, ");
		sql.append(" 	re.`month` ");
		sql.append(" FROM ");
		sql.append(" 	zy_report_exceptions re ");
		sql.append(" LEFT JOIN zy_inspection_report ir on ir.id = re.report_id ");
		sql.append(" LEFT JOIN zy_inspection_plan ip on ip.id = ir.inspection_plan_id ");
		sql.append(" LEFT JOIN zy_equipment_spare es on es.id = ip.equipment_spare_id ");
		sql.append(" WHERE ");
		sql.append(" 	re.`year` = '").append(year).append("' ");
		if(!departIds.equals("")){
			String[] departIdsArr = departIds.split(",");
			sql.append(" AND es.belong_org_id in(");
			for (int i = 0; i < departIdsArr.length; i++) {
				if(i == departIdsArr.length - 1){
					sql.append("'").append(departIdsArr[i]).append("'");
				}else{
					sql.append("'").append(departIdsArr[i]).append("',");
				}
			}
			sql.append(") ");
		}
		if(!esType.equals("")){
			String[] types = esType.split(",");
			sql.append(" AND es.type IN(");
			for (int i = 0; i < types.length; i++) {
				if(i == types.length - 1){
					sql.append("'").append(types[i]).append("'");
				}else{
					sql.append("'").append(types[i]).append("',");
				}
			}
			sql.append(") ");
		}
		sql.append(" GROUP BY ");
		sql.append(" 	re.`month` ");
		List<Map<String, Object>> percentageMapList = super.findForJdbc(sql.toString());
		total = 0;
		Map<String, Object> irExMap = new HashMap<String, Object>();
		for (Map<String, Object> map : percentageMapList) {
			irExMap.put(map.get("month").toString(), map.get("total").toString());
			total += Integer.valueOf(map.get("total").toString());
		}
		irExMap.put("total", total);
		monthTotalMap.put("irExMap", irExMap);
		return monthTotalMap;
	}

	@Override
	public void compareReport(ZyInspectionReportEntity newIr, ZyInspectionReportEntity oldIr,HttpServletRequest req) {
		PropertiesUtil p = new PropertiesUtil("dbconfig.properties");
		try {
			String url = p.readProperty("jdbc.url.jeecg").trim();
			String user = p.readProperty("jdbc.username.jeecg").trim();
			String password = p.readProperty("jdbc.password.jeecg").trim();
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection conn = DriverManager.getConnection(url, user, password);
			Map<String, String> columnsMap = new HashMap<String, String>();
			if(!conn.isClosed() && !conn.isReadOnly()){
				DatabaseMetaData dmd = conn.getMetaData();
				Class clazz = oldIr.getClass();
				Table table = (Table)clazz.getAnnotation(Table.class);
				String tableName = table.name();
				ResultSet rs = dmd.getColumns(null, "%", tableName, "%");
				while(rs.next()){
					String ClmName = rs.getString(4);
					String[] ClmNameArr = ClmName.split("_");
					String newClmName = ClmNameArr[0];
					for (int i = 1; i < ClmNameArr.length; i++) {
						newClmName += ClmNameArr[i].substring(0,1).toUpperCase() + ClmNameArr[i].substring(1,ClmNameArr[i].length());
					}
					columnsMap.put(newClmName, rs.getString(12));
				}
			}
			PropertyDescriptor[] descriptors = PropertyUtils.getPropertyDescriptors(oldIr);
			StringBuffer sb = new StringBuffer();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
			for (int i = 0; i < descriptors.length; i++) {
				String name = descriptors[i].getName();
				if ("class".equals(name) 
						|| "updateName".equals(name) || "updateBy".equals(name) || "updateDate".equals(name)
						|| "createName".equals(name) || "createBy".equals(name) || "createDate".equals(name)) {
					continue;
				}
				try {
					String comment = columnsMap.get(name);
					Object oldValue = PropertyUtils.getSimpleProperty(oldIr, name);
					Object newValue = PropertyUtils.getSimpleProperty(newIr, name);
					String oldType = null == oldValue ? "" :oldValue.getClass().getName();
					String newType = null == newValue ? "" :newValue.getClass().getName();
					/*
					 * 三种情况比较，
					 * 1：数据库为null页面不为null；2：数据库不为null页面为null；3：数据库页面都不为null且值不同
					 */
					if(null == oldValue && null != newValue){
						String nV = newValue.toString();
						if(newType.equals("java.util.Date")){
							nV = DateUtils.date2Str((Date)newValue,sdf);
						}
						sb.append(comment +":由空值变为["+nV+"];");
					}else if(null != oldValue && null == newValue){
						String oV = oldValue.toString();
						if(oldType.equals("java.util.Date")){
							oV = DateUtils.date2Str((Date)oldValue,sdf);
						}else if(oldType.equals("java.lang.Double") && (Double)oldValue == 0.0){
							continue;
						}else if(oldType.equals("java.lang.Integer") && (Integer)oldValue == 0){
							continue;
						}
						sb.append(comment +":由["+oV +"]变为空;");
					}else if(null != oldValue && null != newValue){
						String oV = oldValue.toString();
						String nV = newValue.toString();
						if((oldType.equals("java.sql.Timestamp") && newType.equals("java.util.Date"))
								|| (oldType.equals("java.util.Date") && newType.equals("java.util.Date"))){
							oV = DateUtils.date2Str((Date)oldValue,sdf);
							nV = DateUtils.date2Str((Date)newValue,sdf);
						}else if(oldType.equals("java.lang.Double") && (Double)oldValue == 0.0 && newType.equals("java.lang.Double") && (Double)newValue == 0.0){
							continue;
						}else if(oldType.equals("java.lang.Integer") && (Integer)oldValue == 0 && newType.equals("java.lang.Integer") && (Integer)newValue == 0){
							continue;
						}
						//如果转换成相同格式之后值相同
						if(oV.equals(nV)){
							continue;
						}
						sb.append(comment +":由["+oV +"]变为["+nV+"];");
					}
				}
				catch (java.lang.IllegalArgumentException ie) {
					ie.printStackTrace(); // Should not happen
				}
				catch (Exception e) {
					e.printStackTrace(); // Should not happen
				}
			}
			TSLog tslog = new TSLog();
			tslog.setLogcontent(sb.toString());
			tslog.setTSUser(ResourceUtil.getSessionUserName());
			tslog.setNote(IpUtil.getIpAddr(req));
			tslog.setBroswer(BrowserUtils.getBrowserType(req).toString());
			tslog.setOperatetype((short) 1);
			tslog.setLoglevel((short) 1);
			tslog.setOperatetime(new Timestamp(new Date().getTime()));
			super.save(tslog);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void exportIrReport(String fileName,String tableData,String ipCompany, HttpServletResponse response) {
		OutputStream out = null;
		try {
			response.setCharacterEncoding("text/html;charset=utf-8");
			response.setContentType("application/x-msdownload;");
			response.setHeader("Content-disposition", "attachment; filename="+ (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
//			response.setHeader("Content-Length", "2048");
			String sheetName = "全部";
			if(StringUtil.isNotEmpty(ipCompany)){
				TSDepart depart = super.findUniqueByProperty(TSDepart.class, "orgCode", ipCompany);
				if(null != depart){
					sheetName = depart.getShortname();
				}
			}
			out = response.getOutputStream();
			String[] tableDataArr = tableData.split(";");
			XSSFWorkbook xssf = new XSSFWorkbook();
			XSSFSheet sheet = xssf.createSheet(sheetName);
			int rowNum = 0;
			for (String rowData : tableDataArr) {
				XSSFRow row = sheet.createRow(rowNum);
				String[] cellDataArr = rowData.split(",");
				int cellNum = 0;
				for (String cellData : cellDataArr) {
					XSSFCell cell = row.createCell(cellNum);
					cell.setCellValue(cellData);
					cellNum ++;
				}
				rowNum ++;
			}
			xssf.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally{
			try {
				if(out != null){
					out.flush();
					out.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void checkReportEsItemSN(ZyInspectionReportEntity zyIr) {
		String engItemSn = StringUtil.isNotEmpty(zyIr.getEngItemSn()) ? zyIr.getEngItemSn().trim() : zyIr.getEngItemSn();
		String tiIcpItemSn = StringUtil.isNotEmpty(zyIr.getTiIcpItemSn()) ? zyIr.getTiIcpItemSn().trim() : zyIr.getTiIcpItemSn();
		String lpsItemSn = StringUtil.isNotEmpty(zyIr.getLpsItemSn()) ? zyIr.getLpsItemSn().trim() : zyIr.getLpsItemSn();
		String lps2ItemSn = StringUtil.isNotEmpty(zyIr.getLps2ItemSn()) ? zyIr.getLps2ItemSn().trim() : zyIr.getLps2ItemSn();
		String lps3ItemSn = StringUtil.isNotEmpty(zyIr.getLps3ItemSn()) ? zyIr.getLps3ItemSn().trim() : zyIr.getLps3ItemSn();
		String enigmaItemSn = StringUtil.isNotEmpty(zyIr.getEnigmaItemSn()) ? zyIr.getEnigmaItemSn().trim() : zyIr.getEnigmaItemSn();
		String controllerItemSn = StringUtil.isNotEmpty(zyIr.getControlerItemSn()) ? zyIr.getControlerItemSn().trim() : zyIr.getControlerItemSn();
		String smpsItemSn = StringUtil.isNotEmpty(zyIr.getSmpsItemSn()) ? zyIr.getSmpsItemSn().trim() : zyIr.getSmpsItemSn();
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("	es.sn, es.bp_no, et.type_desc AS esTypeDesc, cm.`name` AS cmName,  es.repair_end_date, ");
		sql.append("	ces.sn AS cSn, ces.item AS cItem, cet.type_desc AS cEsTypeDesc ");
		sql.append(" FROM ");
		sql.append("     zy_inspection_plan ip ");
		sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
		sql.append(" LEFT JOIN zy_equipment_type et on et.id = es.type ");
		sql.append(" left JOIN zy_cinema cm on cm.id = es.belong_cinema_id ");
		sql.append(" LEFT JOIN zy_equipment_spare ces ON ces.top_item_sn = es.sn ");
		sql.append(" LEFT JOIN zy_equipment_type cet ON cet.id = ces.type ");
		sql.append(" WHERE ");
		sql.append("     ip.id = ? ");
		List<Map<String, Object>> reMapList = super.findForJdbc(sql.toString(),zyIr.getInspectionPlanId());
		boolean lpsRight = false;//填写的lps只要有一个符合设备的备件。则不验证其它lps
		List<ZySnCheckRecordEntity> todoSave = new ArrayList<ZySnCheckRecordEntity>();
		for (Map<String, Object> map : reMapList) {
			if(!StringUtil.isNotEmpty(map.get("cEsTypeDesc"))
					|| !StringUtil.isNotEmpty(map.get("cSn"))){
				continue;
			}
			String cSn = map.get("cSn").toString();
			String cEsTypeDesc = map.get("cEsTypeDesc").toString();
			boolean isExcption = false;
			String exceptionSn = "";
			switch(cEsTypeDesc.toLowerCase()){
				case "eng":
					if(!engItemSn.equals(cSn)){
						exceptionSn = engItemSn;
						isExcption = true;
					}
					break;
				case "lps":
					if(!lpsRight){
						if(lpsItemSn.equals(cSn) || lps2ItemSn.equals(cSn) || lps3ItemSn.equals(cSn)){
							lpsRight = true;
							break;
						}
						if(!lpsItemSn.equals(cSn)){
							exceptionSn = lpsItemSn;
							isExcption = true;
						}else if(!lps2ItemSn.equals(cSn)){
							exceptionSn = lps2ItemSn;
							isExcption = true;
						}else if(!lps3ItemSn.equals(cSn)){
							exceptionSn = lps3ItemSn;
							isExcption = true;
						}
					}
					break;
				case "controller":
					if(!controllerItemSn.equals(cSn)){
						exceptionSn = controllerItemSn;
						isExcption = true;
					}
					break;
				case "enigma":
					if(!enigmaItemSn.equals(cSn)){
						exceptionSn = enigmaItemSn;
						isExcption = true;
					}
					break;
				case "smps":
					if(!smpsItemSn.equals(cSn)){
						exceptionSn = smpsItemSn;
						isExcption = true;
					}
					break;
				case "ti/icp":
					if(!tiIcpItemSn.equals(cSn)){
						exceptionSn = tiIcpItemSn;
						isExcption = true;
					}
					break;
			}
			if(isExcption){
				ZySnCheckRecordEntity zySnCheckRecord = new ZySnCheckRecordEntity();
				zySnCheckRecord.setSn(null != map.get("sn") ? map.get("sn").toString() : null);
				zySnCheckRecord.setEsType(null != map.get("esTypeDesc") ? map.get("esTypeDesc").toString() : null);
				zySnCheckRecord.setBp(null != map.get("bp_no") ? map.get("bp_no").toString() : null);
				zySnCheckRecord.setBelongCinemaName(null != map.get("cmName") ? map.get("cmName").toString() : null);
				if(null != map.get("repair_end_date")){
					zySnCheckRecord.setRepairEndDate(DateUtils.str2Date(map.get("repair_end_date").toString(), DateUtils.datetimeFormat));
				}
				zySnCheckRecord.setExceptionDate(new Date());
				zySnCheckRecord.setExceptionSource(StaticValue.DIC_SN_EXCEPTION_SOURCE_INSPECTION);
				zySnCheckRecord.setItemType(cEsTypeDesc);
				zySnCheckRecord.setItem(null != map.get("cItem") ? map.get("cItem").toString() : null);
				zySnCheckRecord.setSysSn(null != map.get("cSn") ? map.get("cSn").toString() : null);
				zySnCheckRecord.setExceptionSn(exceptionSn);
				zySnCheckRecord.setDealResult(StaticValue.DIC_SN_CHECK_DEAL_RESULT_TODO);
				todoSave.add(zySnCheckRecord);
			}
		}
		super.batchSave(todoSave);
	}

	@Override
	public void updateIpAndSavePlanRecord(ZyInspectionReportEntity zyInspectionReport) {
		//保存的时候设置巡检计划的结束时间
		ZyInspectionPlanEntity zyIp = super.get(ZyInspectionPlanEntity.class, zyInspectionReport.getInspectionPlanId());
		zyIp.setState(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE);//设置巡检状态
		zyIp.setBeginTime(zyInspectionReport.getInpBeginTime());
		zyIp.setEndTime(zyInspectionReport.getInpEndTime());
		zyIp.setHadSendWcMsg("0");
		zyIp.setReportCommitTime(new Date());//保存巡检报告保存提交的时间
		super.saveOrUpdate(zyIp);
		//检查巡检报告设备备件序列号
		this.checkReportEsItemSN(zyInspectionReport);
		//保存巡检计划流程记录
		ZyPlanRecordEntity zyPlanRecord = new ZyPlanRecordEntity();
		zyPlanRecord.setBusId(zyIp.getId());
		zyPlanRecord.setPlanState(zyIp.getState());
		zyPlanRecord.setProcInsId(zyIp.getProcesssId());
		zyPlanRecord.setTsUser((TSUser)super.getEntity(TSUser.class, zyIp.getEngineer().getTsBaseUser().getId()));
		zyPlanRecord.setRemark("工程师保存巡检报告");
		super.save(zyPlanRecord);		
	}
	
	@Override
	public void uploadIrAttachment(HttpServletRequest request) {
		try {
			MultipartHttpServletRequest req = (MultipartHttpServletRequest) request;
			MultipartFile multipartFile = req.getFile("ipAttachment");
			String ipId = req.getParameter("ipId");
			String attachmentType = req.getParameter("attachmentType");
			if(StringUtil.isNotEmpty(ipId) && StringUtil.isNotEmpty(attachmentType)){
				ZyInspectionReportEntity zyIr = super.findUniqueByProperty(ZyInspectionReportEntity.class, "inspectionPlanId", ipId);
				if(null != zyIr){
					String suffix = multipartFile.getOriginalFilename().substring  
							(multipartFile.getOriginalFilename().lastIndexOf("."));     
					String filePath = StaticValue.UPLOAD_PATH;
					File fileDirectory = new File(filePath);
					if  (!fileDirectory.exists()  && !fileDirectory.isDirectory()){
						fileDirectory.mkdirs();
					}
					String newFileName = UUID.randomUUID().toString().replace("-", "")+ suffix;
					String fileRealName = multipartFile.getOriginalFilename();
					String uploadPath = filePath + newFileName;
					File file = new File(uploadPath);
					file.createNewFile();
					multipartFile.transferTo(file);
					
					ZyFileLogEntity zyfileLog = new ZyFileLogEntity();
					zyfileLog.setBusinessKey(zyIr.getId());
					zyfileLog.setFilePath(file.getAbsolutePath());
					zyfileLog.setOldFileName(fileRealName);
					zyfileLog.setNewFileName(newFileName);
					super.save(zyfileLog);
					switch(attachmentType){
						case "1":
							attachmentType = StaticValue.DIC_REPORT_ATTACH_TYPE_INSPECTION_IMAGE;
							break;
						case "2":
							attachmentType = StaticValue.DIC_REPORT_ATTACH_TYPE_DIAGNOSE_PACKAGE;
							break;
						case "3":
							attachmentType = StaticValue.DIC_REPORT_ATTACH_TYPE_COMPLETION_IMAGE;
							break;
						default:
							return;
					}
					this.saveAttachment(zyIr.getId(), new String[]{zyfileLog.getId()},attachmentType);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	@Override
	public List<Map<String, Object>> getIpProblemReportDataGroupByTerritory(
			String year, String departIds, String irExType, String ttIds) {
		List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	COUNT(es.city_id) AS total, ");
		sql.append(" 	ptt.territorysort, ");
		sql.append(" 	ptt.id AS ttId, ");
		sql.append(" 	ptt.territoryname, ");
		sql.append(" 	re.`month`, ");
		sql.append(" 	re.`year` ");
		sql.append(" FROM ");
		sql.append(" 	t_s_territory tt ");
		sql.append(" LEFT JOIN t_s_territory ptt ON ptt.id = tt.territoryparentid ");
		sql.append(" LEFT JOIN zy_equipment_spare es ON tt.id = es.city_id ");
		sql.append(" LEFT JOIN zy_inspection_plan ip ON es.id = ip.equipment_spare_id ");
		sql.append(" LEFT JOIN zy_inspection_report ir ON ip.id = ir.inspection_plan_id ");
		sql.append(" LEFT JOIN zy_report_exceptions re ON ir.id = re.report_id ");
		appendCondition(sql, year, departIds, irExType, ttIds);
		sql.append(" GROUP BY ");
		sql.append(" 	ptt.id, ");
		sql.append(" 	re.`month`, ");
		sql.append(" 	re.`year` ");
		sql.append(" ORDER BY ");
		sql.append(" 	ptt.territorysort, ptt.id ");
		List<Map<String, Object>> exMapList = super.findForJdbc(sql.toString());
		sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("	territoryname ");
		sql.append(" FROM ");
		sql.append("	t_s_territory ");
		sql.append(" WHERE ");
		sql.append("	territorylevel = 1 ");
		sql.append(" ORDER BY ");
		sql.append("	territorysort ");
		List<Map<String, Object>> ttMapList = super.findForJdbc(sql.toString());//所有省市 按省市表定义顺序排序
		for (Map<String, Object> map : ttMapList) {
			Map<String, Object> ttMap = new HashMap<String, Object>();
			ttMap.put("territoryname", map.get("territoryname").toString());
			rsMapList.add(ttMap);
		}
		for (Map<String, Object> ttMap : rsMapList) {//按照排序后的省市,捋顺 巡检报告异常数据
			for (Map<String, Object> exMap : exMapList) {
				if(exMap.get("territoryname").toString().equals(ttMap.get("territoryname"))){
					ttMap.put(exMap.get("month").toString(), exMap.get("total").toString());
					ttMap.put("ttId", exMap.get("ttId").toString());
				}
			}
		}
		return rsMapList;
	}

	@Override
	public List<Highchart> queryIpProgressReportData(String year, String departIds) {
		List<Highchart> highCharts = new ArrayList<Highchart>();
		List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();
		String bpCodeStr = "";
		if(!departIds.equals("")){
			String[] departIdsArr = departIds.split(",");
			String departIdsStr = "";
			for (String id : departIdsArr) {
				departIdsStr += "'"+id+"',";
			}
			departIdsStr = departIdsStr.substring(0,departIdsStr.length()-1);
			List<Map<String, Object>> mapList = super.findForJdbc("SELECT bp_code FROM t_s_depart WHERE id in ("+departIdsStr+")");
			for (Map<String, Object> map : mapList) {
				if(map.get("bp_code") != null){
					bpCodeStr += "'" + map.get("bp_code").toString() +"'," ;
				}
			}
			bpCodeStr = !bpCodeStr.equals("") ? bpCodeStr.substring(0,bpCodeStr.length() - 1) : bpCodeStr;
		}
		/*-----------------------计划单月巡检量-------------------------*/
		StringBuffer sql = new StringBuffer();
		if(bpCodeStr.equals("")){
			//第一次进入 没有选择 BP 查询所有
			rsMapList = super.findForJdbc("SELECT COUNT(1) AS total, MONTH(ip.ip_plan_date) AS _month FROM zy_inspection_plan ip" +
					" WHERE YEAR(ip_plan_date) = ? AND ip.state <> ? GROUP BY _month", year, StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL);
		}else{
			sql.append(" SELECT COUNT(1) AS total, MONTH(ip.ip_plan_date) AS _month FROM ");
			sql.append(" 	zy_inspection_plan ip ");
			sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
			sql.append(" WHERE ");
			sql.append(" 	es.bp_no IN ("+bpCodeStr+") ");
			sql.append(" AND YEAR (ip.ip_plan_date) = ? ");
			sql.append(" AND ip.state <> ? ");
			sql.append(" GROUP BY _month");
			rsMapList = super.findForJdbc(sql.toString(), year ,StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL);
		}
		List<Integer> hcSingleMonthList = new ArrayList<Integer>();

		pushData(rsMapList, hcSingleMonthList);
//		for (int i = 0; i < 12; i++) {
//			boolean had = false;
//			for (Map<String,Object> map : rsMapList) {
//				if(map.get("_month").toString().equals(String.valueOf(i+1))){
//					hcSingleMonthList.add(Integer.valueOf(map.get("total").toString()));
//					had = true;
//					break;
//				}
//			}
//			if(!had || rsMapList.size() == 0){
//				hcSingleMonthList.add(0);
//			}
//		}
		Highchart hcSingleMonth = new Highchart();
		hcSingleMonth.setName("计划单月巡检量");
		hcSingleMonth.setData(hcSingleMonthList);
		hcSingleMonth.setStack("stact1");
		highCharts.add(hcSingleMonth);
		/*---------------------实际单月巡检量---------------------------*/
		List<Map<String, Object>> actSingleMonthCountMapList = new ArrayList<Map<String,Object>>();
		if(bpCodeStr.equals("")){
			//第一次进入 没有选择 BP 查询所有
			actSingleMonthCountMapList = super.findForJdbc("SELECT count(1) AS total, MONTH(ip_plan_date) AS _month FROM zy_inspection_plan WHERE YEAR(ip_plan_date) = ? AND state = ? GROUP BY _month" , year, StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE);
		}else{
			sql = new StringBuffer();
			sql.append(" SELECT count(1) AS total, MONTH (ip.ip_plan_date) AS _month ");
			sql.append(" FROM ");
			sql.append(" 	zy_inspection_plan ip ");
			sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
			sql.append(" WHERE ");
			sql.append(" 	es.bp_no IN ("+bpCodeStr+") ");
			sql.append(" AND YEAR (ip.ip_plan_date) = ? ");
			sql.append(" AND ip.state = ? ");
			sql.append(" GROUP BY ");
			sql.append(" 	_month ");
			actSingleMonthCountMapList = super.findForJdbc(sql.toString() , year, StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE);
		}
		List<Integer> actSingleMonthCountList = new ArrayList<Integer>();
		pushData(actSingleMonthCountMapList, actSingleMonthCountList);
//		for (int i = 0; i < 12; i++) {
//			boolean had = false;
//			for (Map<String,Object> map : actSingleMonthCountMapList) {
//				if(map.get("_month").toString().equals(String.valueOf(i+1))){
//					actSingleMonthCountList.add(Integer.valueOf(map.get("total").toString()));
//					had = true;
//					break;
//				}
//			}
//			if(!had || actSingleMonthCountMapList.size() == 0){
//				actSingleMonthCountList.add(0);
//			}
//		}
		Highchart hcActSingleMonth = new Highchart();
		hcActSingleMonth.setName("实际单月巡检量");
		hcActSingleMonth.setData(actSingleMonthCountList);
		hcActSingleMonth.setStack("stact2");
		highCharts.add(hcActSingleMonth);
		return highCharts;
	}
	
	//适用于 12个月
	private void pushData(List<Map<String, Object>> dataMapList,List<Integer> pushList){
		for (int i = 0; i < 12; i++) {
			boolean had = false;
			for (Map<String,Object> map : dataMapList) {
				if(map.get("_month").toString().equals(String.valueOf(i+1))){
					pushList.add(Integer.valueOf(map.get("total").toString()));
					had = true;
					break;
				}
			}
			if(!had || dataMapList.size() == 0){
				pushList.add(0);
			}
		}
	}
	
	@Override
	public List<Highchart> queryIpProgressReportDataDuiDie(boolean tpRoleUser, String year,String departIds,String egIds) {
		List<Highchart> highCharts = new ArrayList<Highchart>();
		StringBuilder sql = new StringBuilder();
		if(!departIds.equals("")){
			departIds = "'" + departIds.replace(",", "\',\'") + "'";
		}
		String egIdStr = "";
		if(StringUtil.isNotEmpty(egIds)){
			egIdStr = "'" + egIds.replace(",","','") + "'";
		}
		sql.append(" SELECT ");
		sql.append(" 	MONTH (report_commit_time) _month, ");
		sql.append(" 	SUM(CASE WHEN MONTH(report_commit_time) = MONTH(ip_plan_date) THEN 1 ELSE 0 END) _eq, ");
		sql.append(" 	SUM(CASE WHEN MONTH(report_commit_time) <> MONTH(ip_plan_date) THEN 1 ELSE 0 END) _unEq ");
		sql.append(" FROM ");
		sql.append(" 	zy_inspection_plan ip ");
//		sql.append(" 	LEFT JOIN t_s_depart dp ON dp.org_code = ip.company ");
		sql.append(" WHERE  1 = 1 ");
		sql.append("	AND (");
		sql.append(" 	YEAR (report_commit_time) = ").append(year).append(" ");
		sql.append(" 	AND YEAR (ip_plan_date) = ").append(year).append(" ");
		//涉及到跨年，比如16年的计划 17年完成的（计划外）
		sql.append(" 	OR( YEAR (report_commit_time) = ").append(year).append(" AND YEAR (ip_plan_date) = ").append(Integer.valueOf(year) -1).append(" )");
		sql.append("	)");
		sql.append(" 	AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
		if(!departIds.equals("")){
			sql.append("	AND company IN("+ departIds +")");
		}
		if(StringUtil.isNotEmpty(egIdStr)){
			sql.append("	AND engineer IN ("+ egIdStr +") ");
		}
		sql.append(" GROUP BY ");
		sql.append(" 	MONTH (report_commit_time);");
		//左边的
		List<Map<String,Object>> rsLeftMapList = super.findForJdbc(sql.toString());
		List<Integer> eqList = new ArrayList<Integer>();
		List<Integer> nEqList = new ArrayList<Integer>();
		
		//适用于 12个月
		for (int i = 0; i < 12; i++) {
			boolean had = false;
			for (Map<String,Object> map : rsLeftMapList) {
				if(map.get("_month").toString().equals(String.valueOf(i+1))){
					eqList.add(Integer.valueOf(map.get("_eq").toString()));
					nEqList.add(Integer.valueOf(map.get("_unEq").toString()));
					had = true;
					break;
				}
			}
			if(!had || rsLeftMapList.size() == 0){
				eqList.add(0);
				nEqList.add(0);
			}
		}
		Highchart hgEq = new Highchart();
		hgEq.setName("计划内完成");
		hgEq.setData(eqList);
		hgEq.setStack("stk1");
		Highchart hgNEq = new Highchart();
		hgNEq.setName("计划外完成");
		hgNEq.setData(nEqList);
		hgNEq.setStack("stk1");
		
		highCharts.add(hgEq);
		highCharts.add(hgNEq);
		
		
		sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" 	MONTH (ip.ip_plan_date) _month, ");
		sql.append(" 	SUM(CASE WHEN ip.generate_source = '").append(StaticValue.DIC_GENERATE_SOURCE_AUTO).append("' THEN 1 ELSE 0 END) auto, ");
		sql.append(" 	SUM(CASE WHEN ip.generate_source = '").append(StaticValue.DIC_GENERATE_SOURCE_REAL_TIME).append("' THEN 1 ELSE 0 END) real_time, ");
		sql.append(" 	SUM(CASE WHEN ip.generate_source = '").append(StaticValue.DIC_GENERATE_SOURCE_MANUAL).append("' THEN 1 ELSE 0 END) manual ");
		sql.append(" FROM ");
		sql.append(" 	zy_inspection_plan ip ");
//		sql.append(" 	LEFT JOIN t_s_depart dp ON dp.org_code = ip.company ");
		sql.append(" WHERE 1=1 ");
		sql.append(" 	AND ip.state != '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL).append("' ");
		sql.append(" 	AND YEAR (ip_plan_date) = ").append(year).append(" ");
		if(!departIds.equals("")){
			sql.append("	AND company IN("+ departIds +")");
		}
		if(StringUtil.isNotEmpty(egIdStr)){
			sql.append("	AND engineer IN ("+ egIdStr +") ");
		}
		sql.append(" GROUP BY ");
		sql.append(" 	MONTH (ip.ip_plan_date) ");
		//中间
		List<Map<String,Object>> rsCenterMapList = super.findForJdbc(sql.toString());
		List<Integer> autoList = new ArrayList<Integer>();
		List<Integer> real_time = new ArrayList<Integer>();
		List<Integer> manual = new ArrayList<Integer>();
		//适用于 12个月
		for (int i = 0; i < 12; i++) {
			boolean had = false;
			for (Map<String,Object> map : rsCenterMapList) {
				if(map.get("_month").toString().equals(String.valueOf(i+1))){
					autoList.add(Integer.valueOf(map.get("auto").toString()));
					real_time.add(Integer.valueOf(map.get("real_time").toString()));
					manual.add(Integer.valueOf(map.get("manual").toString()));
					had = true;
					break;
				}
			}
			if(!had || rsCenterMapList.size() == 0){
				autoList.add(0);
				real_time.add(0);
				manual.add(0);
			}
		}
		Highchart hgAuto = new Highchart();
		hgAuto.setName("年度计划");
		hgAuto.setColor("#44A9A8");
		hgAuto.setData(autoList);
		hgAuto.setStack("stk2");
		Highchart hgRealTime = new Highchart();
		hgRealTime.setName("实时新增");
		hgRealTime.setData(real_time);
		hgRealTime.setStack("stk2");
		Highchart hgManual = new Highchart();
		hgManual.setName("手动创建");
		hgManual.setData(manual);
		hgManual.setStack("stk2");
		
		highCharts.add(hgAuto);
		highCharts.add(hgRealTime);
		highCharts.add(hgManual);
		
		sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" 	MONTH (ip.ip_plan_date) _month, ");
		sql.append(" 	COUNT(*) total ");
		sql.append(" FROM ");
		sql.append(" 	zy_inspection_plan ip ");
//		sql.append(" 	LEFT JOIN t_s_depart dp ON dp.org_code = ip.company ");
		sql.append(" WHERE 1=1 ");
		sql.append(" 	AND YEAR (ip_plan_date) = ").append(year).append(" ");
		sql.append(" 	AND ip.state NOT IN ('").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("','").append(StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL).append("') ");
		if(!departIds.equals("")){
			sql.append("	AND company IN("+ departIds +")");
		}
		if(StringUtil.isNotEmpty(egIdStr)){
			sql.append("	AND engineer IN ("+ egIdStr +") ");
		}
		sql.append(" GROUP BY ");
		sql.append(" 	MONTH (ip.ip_plan_date) ");
		//右边
		List<Map<String,Object>> rsRightMapList = super.findForJdbc(sql.toString());
		List<Integer> rightData = new ArrayList<Integer>();
		pushData(rsRightMapList, rightData);
		
		Highchart hg = new Highchart();
		hg.setColor("rgb(189, 33, 4)");
		hg.setName("本月计划内未完成");
		hg.setData(rightData);
		hg.setStack("stk3");

		highCharts.add(hg);
		
		if(tpRoleUser){
			// 0 1 <> 2 3 4
			// 3 4 2 <> 0 1
			// 2 3 4 <> 0 1
			Collections.swap(highCharts, 0, 3);
			Collections.swap(highCharts, 1, 4);
			Collections.swap(highCharts, 2, 0);
			Collections.swap(highCharts, 1, 2);
		}
		return highCharts;
	}
	

	@Override
	public List<ComboTree> getEsTypesTreeForIrReport() {
		//"typeDesc","放映机"
		List<ZyEquipmentTypeEntity> territoryList = super.findByQueryString("from ZyEquipmentTypeEntity where typeDesc = '放映机' ");
        List<ComboTree> comboTrees = new ArrayList<ComboTree>();
        ComboTreeModel comboTreeModel = new ComboTreeModel("id", "typeDesc", "childEquipmentTypes");
        comboTrees = super.comboTree(territoryList, comboTreeModel, null, true);
        return comboTrees;
	}

	@Override
	public List<Highchart> queryIpReportData(String company, String year, String type, String egIds){
		List<Highchart> rsList = new ArrayList<Highchart>();
		String math = "";
		int count = 0;
		if(type.equals("month")){
			math = "MONTH";
			count = 12;
		}else if(type.equals("week")){
			math = "WEEK";
			count = 52;
		}
		String egIdStr = "";
		if(StringUtil.isNotEmpty(egIds)){
			egIdStr = "'" + egIds.replace(",","','") + "'";
		}
		/*==================已指派数量 ↓↓↓↓↓======================*/
		String alreadySendState = "'" + StaticValue.DIC_PLAN_STATE_TODO + "'," +
								"'" + StaticValue.DIC_PLAN_STATE_THIRDPARTY_REFUSED + "'," +
								"'" + StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL + "'";
		Map<String, Object> rsMap = null;
		List<Map<String, Object>> alreadySendMapList = new ArrayList<Map<String,Object>>();
		StringBuilder sql = null;
		if(type.equals("month")){
			sql = new StringBuilder();
			sql.append(" SELECT ");
			sql.append("	COUNT(1) AS total, MONTH(ip.ip_plan_date) AS _month ");
			sql.append(" FROM zy_inspection_plan ip ");
			sql.append(" WHERE ");
			sql.append("	YEAR(ip_plan_date) = ? ");
			sql.append("	AND state NOT IN("+alreadySendState+") ");
			if(StringUtil.isNotEmpty(company)){
				sql.append("	AND company = '").append(company).append("' ");
			}
			if(StringUtil.isNotEmpty(egIdStr)){
				sql.append("	AND engineer IN ("+ egIdStr +") ");
			}
			sql.append(" GROUP BY _month");
			alreadySendMapList = super.findForJdbc(sql.toString(), year);
		}else if(type.equals("week")){
			sql = new StringBuilder();
			sql.append(" SELECT ");
			sql.append("	COUNT(1) AS total ");
			sql.append(" FROM zy_inspection_plan ip  ");
			sql.append(" WHERE ");
			sql.append("	YEAR(ip_plan_date) = ? ");
			sql.append("	AND state NOT IN("+alreadySendState+") ");
			if(StringUtil.isNotEmpty(company)){
				sql.append("	AND company = '").append(company).append("' ");
			}
			if(StringUtil.isNotEmpty(egIdStr)){
				sql.append("	AND engineer IN ("+ egIdStr +") ");
			}
			rsMap = super.findOneForJdbc(sql.toString(), year);
		}
		
		List<Integer> alreadySendList = new ArrayList<Integer>();
		if(type.equals("month")){
			for (int i = 0; i < 12; i++) {
				boolean had = false;
				for (Map<String,Object> map : alreadySendMapList) {
					if(map.get("_month").toString().equals(String.valueOf(i+1))){
						alreadySendList.add(Integer.valueOf(map.get("total").toString()));
						had = true;
						break;
					}
				}
				if(!had || alreadySendMapList.size() == 0){
					alreadySendList.add(0);
				}
			}
		}else if(type.equals("week")){
			int totalInYear = Integer.valueOf(rsMap.get("total").toString());
			Integer alreadySend = (Integer)Integer.valueOf(totalInYear) / count;
			for (int i = 0; i < count; i++) {
				if(i < (count - 1)){
					alreadySendList.add(alreadySend);
				}else{
					alreadySendList.add(totalInYear - alreadySend * (count - 1));
				}
			}
		}
		
		Highchart alreadySendHc = new Highchart();
		alreadySendHc.setName("已指派数量");
		alreadySendHc.setData(alreadySendList);
		rsList.add(alreadySendHc);
		/*===================已完成数量 ↓↓↓↓↓=====================*/
		List<Map<String, Object>> reList = null;
		sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append("	COUNT(1) AS total, ");
		sql.append(math+"(ip_plan_date) as _"+math);
		sql.append(" FROM ");
		sql.append("	zy_inspection_plan");
		sql.append(" WHERE ");
		sql.append("	YEAR(ip_plan_date) = ? ");
		sql.append("	AND state = ? ");
		if(StringUtil.isNotEmpty(company)){
			sql.append("	AND company = '").append(company).append("' ");
		}
		if(StringUtil.isNotEmpty(egIdStr)){
			sql.append("	AND engineer IN ("+ egIdStr +") ");
		}
		sql.append(" GROUP BY _"+math);
		reList = super.findForJdbc(sql.toString(), year, StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE);
		
		List<Integer> alreadyDoneList = new ArrayList<Integer>();
		for (int i = 0; i < count; i++) {
			boolean had = false;
			for (Map<String,Object> map : reList) {
				if(map.get("_"+math).toString().equals(String.valueOf(i+1))){
					alreadyDoneList.add(Integer.valueOf(map.get("total").toString()));
					had = true;
					break;
				}
			}
			if(!had || reList.size() == 0){
				alreadyDoneList.add(0);
			}
		}
		Highchart alreadyDoneHc = new Highchart();
		alreadyDoneHc.setName("已完成数量");
		alreadyDoneHc.setData(alreadyDoneList);
		rsList.add(alreadyDoneHc);
		/*====================未完成数量 ↓↓↓↓↓====================*/
		List<Integer> didNotComplateList = new ArrayList<Integer>();
		for (int i = 0; i < count; i++) {
			Integer result = Integer.valueOf(alreadySendList.get(i)) - Integer.valueOf(alreadyDoneList.get(i));
			didNotComplateList.add(result);
		}
		Highchart didNotComplateHc = new Highchart();
		didNotComplateHc.setName("未完成数量");
		didNotComplateHc.setData(didNotComplateList);
		rsList.add(didNotComplateHc);
		/*====================拒单数量 ↓↓↓↓↓====================*/
		List<Map<String, Object>> refuseMapList = null;
		sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append("	count(1) AS total, ");
		sql.append(math+"(ip_plan_date) AS _"+math);
		sql.append(" FROM ");
		sql.append("	zy_inspection_plan ");
		sql.append(" WHERE ");
		sql.append("	year(ip_plan_date) = ? ");
		sql.append("	AND state =? ");
		sql.append("");
		if(StringUtil.isNotEmpty(company)){
			sql.append("	AND company = '").append(company).append("' ");
		}
		if(StringUtil.isNotEmpty(egIdStr)){
			sql.append("	AND engineer IN ("+ egIdStr +") ");
		}
		sql.append("GROUP BY _"+math);
		refuseMapList = super.findForJdbc(sql.toString(), year, StaticValue.DIC_PLAN_STATE_THIRDPARTY_REFUSED);
		
		List<Integer> refuseList = new ArrayList<Integer>();
		for (int i = 0; i < count; i++) {
			boolean had = false;
			for (Map<String,Object> map : refuseMapList) {
				if(map.get("_"+math).toString().equals(String.valueOf(i+1))){
					refuseList.add(Integer.valueOf(map.get("total").toString()));
					had = true;
					break;
				}
			}
			if(!had || refuseMapList.size() == 0){
				refuseList.add(0);
			}
		}
		Highchart refuseHc = new Highchart();
		refuseHc.setName("拒单数量");
		refuseHc.setData(refuseList);
		rsList.add(refuseHc);
		return rsList;
	}

	@Override
	public ZyInspectionReportEntity addReportFirst(String planId) {
		ZyInspectionReportEntity zyIr = new ZyInspectionReportEntity();
		zyIr.setInspectionPlanId(planId);
		super.save(zyIr);
		ZyInspectionReportEntity copyZyIr = new ZyInspectionReportEntity();
		SimpleDateFormat sdf = DateUtils.datetimeFormat;
		try {
			MyBeanUtils.copyBean2Bean(copyZyIr, zyIr);
			//这里需要格式化一下，否则保存时绑定值会报错
			if(null != copyZyIr.getCreateDate()){
				copyZyIr.setCreateDate(DateUtils.getTimestamp(sdf.parse(sdf.format(copyZyIr.getCreateDate())).getTime()));
			}
		} catch (ParseException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return copyZyIr;
	}

	@Override
	public List<JSONObject> queryEachCityPillarData(String year, String departIds, String irExType, String ttIds) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	SUM(temperature_ex + humidity_ex + coolant_pressure_ex + coolant_height_ex + brightness_ex + wind_speed_ex + zero_ground_voltage_ex + training_num_ex) AS total,t.* ");
		sql.append(" FROM ");
		sql.append(" ( ");
		sql.append(" 	SELECT ");
		sql.append(" 		IFNULL(SUM(re.is_temperature_ex),0) AS temperature_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_humidity_ex),0) AS humidity_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_coolant_pressure_ex),0) AS coolant_pressure_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_coolant_height_ex),0) AS coolant_height_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_brightness_ex),0) AS brightness_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_wind_speed_ex),0) AS wind_speed_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_zero_ground_voltage_ex),0) AS zero_ground_voltage_ex, ");
		sql.append(" 		IFNULL(SUM(re.is_training_num_ex),0) AS training_num_ex, ");
		sql.append(" 		ptt.id AS ttId, ");
		sql.append(" 		ptt.territoryname ");
		sql.append(" 	FROM ");
		sql.append(" 		t_s_territory tt ");
		sql.append(" 	LEFT JOIN t_s_territory ptt ON ptt.id = tt.territoryparentid ");
		sql.append(" 	LEFT JOIN zy_equipment_spare es ON tt.id = es.city_id ");
		sql.append(" 	LEFT JOIN zy_inspection_plan ip ON es.id = ip.equipment_spare_id ");
		sql.append(" 	LEFT JOIN zy_inspection_report ir ON ip.id = ir.inspection_plan_id ");
		sql.append(" 	LEFT JOIN zy_report_exceptions re ON ir.id = re.report_id ");
		appendCondition(sql, year, departIds, irExType, ttIds);
		sql.append(" 	GROUP BY ");
		sql.append(" 		ptt.id ");
		sql.append(" ) t ");
		sql.append(" GROUP BY ");
		sql.append(" 	t.ttId ");
		sql.append(" ORDER BY ");
		sql.append(" 	total DESC ");
		List<Map<String, Object>> rsMapList = super.findForJdbc(sql.toString());
		
		List<Integer> temperature_exData = new ArrayList<Integer>();
		List<Integer> humidity_exData = new ArrayList<Integer>();
		List<Integer> coolant_pressure_exData = new ArrayList<Integer>();
		List<Integer> coolant_height_exData = new ArrayList<Integer>();
		List<Integer> brightness_exData = new ArrayList<Integer>();
		List<Integer> wind_speed_exData = new ArrayList<Integer>();
		List<Integer> zero_ground_voltage_exData = new ArrayList<Integer>();
		List<Integer> training_num_exData = new ArrayList<Integer>();
		List<JSONObject> jsonData = new ArrayList<JSONObject>();
		List<String> citys = new ArrayList<String>();
		/** 地图上数据累加为 0 的也需要显示颜色，所以这里根据现有的省覆盖一下*/
		sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("	territoryname,id ");
		sql.append(" FROM ");
		sql.append("	t_s_territory ");
		sql.append(" WHERE ");
		sql.append("	territorylevel = 1 ");
		sql.append(" ORDER BY ");
		sql.append("	territorysort ");
		List<Map<String, Object>> ttMapList = super.findForJdbc(sql.toString());//所有省市 按省市表定义顺序排序
		for (Map<String, Object> map : ttMapList) {
			boolean had = false;
			for (Map<String, Object> rsMap : rsMapList) {
				if(rsMap.get("ttId").toString().equals(map.get("id").toString())){
					had = true;
				}
			}
			if(!had){
				Map<String,Object> nMap = new HashMap<String, Object>();
				if (rsMapList != null && rsMapList.size() > 0) { 
					for(String key : rsMapList.get(0).keySet()){
						nMap.put(key, 0);
					}
					nMap.put("ttId",map.get("id"));
					nMap.put("territoryname",map.get("territoryname"));
					rsMapList.add(nMap);
				}
			}
		}
		/** 地图上数据累加为 0 的也需要显示颜色，所以这里根据现有的省覆盖一下------------end---*/
		for (Map<String, Object> map : rsMapList) {
			citys.add(map.get("territoryname").toString());
			
			temperature_exData.add(Integer.valueOf(map.get("temperature_ex").toString()));
			humidity_exData.add(Integer.valueOf(map.get("humidity_ex").toString()));
			coolant_pressure_exData.add(Integer.valueOf(map.get("coolant_pressure_ex").toString()));
			coolant_height_exData.add(Integer.valueOf(map.get("coolant_height_ex").toString()));
			brightness_exData.add(Integer.valueOf(map.get("brightness_ex").toString()));
			wind_speed_exData.add(Integer.valueOf(map.get("wind_speed_ex").toString()));
			zero_ground_voltage_exData.add(Integer.valueOf(map.get("zero_ground_voltage_ex").toString()));
			training_num_exData.add(Integer.valueOf(map.get("training_num_ex").toString()));
		}
		jsonData.add(new JSONObject().accumulate("citys", citys));//前台需要单独取出
		
		jsonData.add(new JSONObject().accumulate("name", "温度异常").accumulate("data", temperature_exData).accumulate("color", "#C23531"));
		jsonData.add(new JSONObject().accumulate("name", "湿度过大").accumulate("data", humidity_exData).accumulate("color", "#2F4554"));
		jsonData.add(new JSONObject().accumulate("name", "冷却液压力异常").accumulate("data", coolant_pressure_exData).accumulate("color", "#61A0A8"));
		jsonData.add(new JSONObject().accumulate("name", "冷却液面高度异常").accumulate("data", coolant_height_exData).accumulate("color", "#D48265"));
		jsonData.add(new JSONObject().accumulate("name", "亮度低").accumulate("data", brightness_exData).accumulate("color", "#C23531"));
		jsonData.add(new JSONObject().accumulate("name", "风速异常").accumulate("data", wind_speed_exData).accumulate("color", "#749F83"));
		jsonData.add(new JSONObject().accumulate("name", "零地电压异常").accumulate("data", zero_ground_voltage_exData).accumulate("color", "#CA8622"));
		jsonData.add(new JSONObject().accumulate("name", "参加培训人数不足").accumulate("data", training_num_exData).accumulate("color", "#BDA29A"));
		return jsonData;
	}

	//为巡检问题分析报表拼接条件
	private void appendCondition(StringBuffer sql,String year, String departIds, String irExType, String ttIds){
		sql.append(" WHERE ");
		sql.append("	re.`year` = '").append(year).append("' ");
		if(StringUtil.isNotEmpty(departIds)){
			String[] departIdArr = departIds.split(",");
			sql.append(" AND es.belong_org_id in(");
			for (int i = 0; i < departIdArr.length; i++) {
				if(i != departIdArr.length - 1){
					sql.append("'").append(departIdArr[i]).append("',");
				}else{
					sql.append("'").append(departIdArr[i]).append("'");
				}
			}
			sql.append(") ");
		}
		if(StringUtil.isNotEmpty(ttIds)){
			String[] ttIdArr = ttIds.split(",");
			sql.append(" AND ptt.id in(");
			for (int i = 0; i < ttIdArr.length; i++) {
				if(i != ttIdArr.length - 1){
					sql.append("'").append(ttIdArr[i]).append("',");
				}else{
					sql.append("'").append(ttIdArr[i]).append("'");
				}
			}
			sql.append(") ");
		}
		
		if(StringUtil.isNotEmpty(irExType)){
			switch(irExType){
				case StaticValue.DIC_IR_EX_TYPE_A://零地电压异常
					sql.append(" AND re.is_zero_ground_voltage_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_B://湿度异常
					sql.append(" AND re.is_humidity_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_C://温度异常
					sql.append(" AND re.is_temperature_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_D://冷却液压力异常
					sql.append(" AND re.is_coolant_pressure_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_E://冷却液面高度异常
					sql.append(" AND re.is_coolant_height_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_F://亮度低
					sql.append(" AND re.is_brightness_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_G://风速异常
					sql.append(" AND re.is_wind_speed_ex = 1");
					break;
				case StaticValue.DIC_IR_EX_TYPE_H://参加培训人数不足
					sql.append(" AND re.is_training_num_ex = 1");
					break;
			}
		}
	}

	@Override
	public void queryDataForIpListOfIpProblemAnalysisReport(HttpServletRequest request, DataGrid dataGrid) {
		try {
			
			//从巡检问题分析报表跳转过来的 
			String year = request.getParameter("year");//年份
			String month = request.getParameter("month");//月份
			String exType = oConvertUtils.getString(request.getParameter("exType"));//异常类型
			String ttId = request.getParameter("ttId");//省份Id
			String departIds = request.getParameter("departIds");//机构Id
			String esTypes = request.getParameter("esTypes");//机型Id
			
			String ipEndTimeYear = oConvertUtils.getString(request.getParameter("ipEndTimeYear"));
			String ipEndTimeMonth = oConvertUtils.getString(request.getParameter("ipEndTimeMonth"));
	
			/*
			 * year是基础条件，月份 为空是点月份的总计，否则是具体月份 ，【省份id】是从区域汇总点进来的，【异常类型】是从异常类型汇总点进来的（-1代表‘有问题的巡检任务数’）
			 * ipEndTimeYear 和 ipEndTimeMonth 是从各机型问题汇总表格中‘巡检任务总数’点进来的
			 */
			List<Map<String, Object>> datagridList = new ArrayList<Map<String, Object>>();
			StringBuilder sql = new StringBuilder();
			sql.append(" SELECT ");
			sql.append(" 	equipment_type_id, ");
			sql.append(" 	CASE maintain_project  ");
			sql.append(" 		WHEN 'zeroGroundVoltage' THEN 'A' ");
			sql.append(" 		WHEN 'humidity' THEN 'B' ");
			sql.append(" 		WHEN 'temperature' THEN 'C' ");
			sql.append(" 		WHEN 'coolantPressure' THEN 'D' ");
			sql.append(" 		WHEN 'coolantHeight' THEN 'E' ");
			sql.append(" 		WHEN 'brightness' THEN 'F' ");
			sql.append(" 		WHEN 'windSpeed' THEN 'G' ");
			sql.append(" 		WHEN 'trainingNum' THEN 'H' ");
			sql.append(" 	END AS exType, ");
			sql.append("	CASE  ");
			sql.append("		WHEN ISNULL(min) && !ISNULL(max) THEN CONCAT(max, unit) ");
			sql.append("		WHEN !ISNULL(min) && ISNULL(max) THEN CONCAT(min, unit) ");
			sql.append("		WHEN !ISNULL(min) && !ISNULL(max) THEN CONCAT(min, ' ~ ', max, ' ', unit) ");
			sql.append(" 	END AS value ");
			sql.append(" FROM ");
			sql.append(" 	zy_em_valida_rule ");
			//机型 验证规则 
			List<Map<String, Object>> esTypeRulesMapList = findForJdbc(sql.toString());
			Map<String, Object> esTypeRules = new HashMap<>();
			for (Map<String, Object> map : esTypeRulesMapList) {
				esTypeRules.put(map.get("equipment_type_id").toString() + map.get("exType").toString(), map.get("value").toString());
			}
			
			sql = new StringBuilder();
			sql.append(" SELECT ");
			sql.append(" 	ip.id, ");
			sql.append("	IFNULL(re.is_zero_ground_voltage_ex,'') AS is_zero_ground_voltage_ex, re.zero_ground_voltage_ex_val, ");
			sql.append("	IFNULL(re.is_humidity_ex,'') AS is_humidity_ex, re.humidity_ex_val, ");
			sql.append("	IFNULL(re.is_temperature_ex,'') AS is_temperature_ex, re.temperature_ex_val, ");
			sql.append("	IFNULL(re.is_coolant_pressure_ex,'') AS is_coolant_pressure_ex, re.coolant_pressure_ex_val, ");
			sql.append("	IFNULL(re.is_coolant_height_ex,'') AS is_coolant_height_ex, re.coolant_height_ex_val, ");
			sql.append("	IFNULL(re.is_brightness_ex,'') AS is_brightness_ex, re.brightness_ex_val, ");
			sql.append("	IFNULL(re.is_wind_speed_ex,'') AS is_wind_speed_ex, re.wind_speed_ex_val, ");
			sql.append("	IFNULL(re.is_training_num_ex,'') AS is_training_num_ex, re.training_num_ex_val, ");
			sql.append(" 	ip.plan_id AS planId, ");
			sql.append(" 	es.sn AS esSn, ");
			sql.append(" 	et.id AS esTypeId, ");
			sql.append(" 	et.type_desc AS esType, ");
			sql.append(" 	cm.NAME AS cmName, ");
			sql.append(" 	IFNULL(dp.departname ,'-')AS cmDepartName, ");
			sql.append(" 	CONCAT(ptt.territoryname, tt.territoryname) AS pttAndtt, ");
			sql.append(" 	ip.ip_plan_date AS ipPlanDate, ");
			sql.append(" 	ipDp.shortName AS ipDepartName, ");
			sql.append(" 	eg.`name` AS egName ");
			sql.append(" FROM ");
			sql.append(" 	zy_report_exceptions re ");
			StringBuilder joinSql = new StringBuilder();
			joinSql.append(" INNER JOIN zy_inspection_report ir ON ir.id = re.report_id ");
			joinSql.append(" LEFT JOIN zy_inspection_plan ip ON ip.id = ir.inspection_plan_id ");
			joinSql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
			joinSql.append(" LEFT JOIN zy_equipment_type et ON et.id = es.type ");
			joinSql.append(" LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");
			joinSql.append(" LEFT JOIN t_s_depart dp ON dp.id = cm.belong_org ");
			joinSql.append(" LEFT JOIN t_s_territory tt ON tt.ID = es.city_id ");
			joinSql.append(" LEFT JOIN t_s_territory ptt ON ptt.id = tt.territoryparentid ");
			joinSql.append(" LEFT JOIN t_s_depart ipDp ON ipDp.org_code = ip.company ");
			joinSql.append(" LEFT JOIN zy_engineer eg ON eg.id = ip.engineer ");
			StringBuilder conditionSql = new StringBuilder();
			sql.append(joinSql.toString());
			sql.append(" WHERE 1=1 ");
			if(StringUtil.isNotEmpty(year)){//year是基础查询条件，这里根据year 和 ipEndTimeYear 来区分
				conditionSql.append("   AND re.`year` = '").append(year).append("' ");
		        if(StringUtil.isNotEmpty(month)){
		        	conditionSql.append(" AND re.`month` = '").append(month).append("' ");
				}
		        if (StringUtil.isNotEmpty(exType)) {//如果exType有值，则是在 机型汇总里点击进来的
		    		if(!departIds.equals("")){
		    			String[] departIdsArr = departIds.split(",");
		    			sql.append(" AND es.belong_org_id IN(");
		    			for (int i = 0; i < departIdsArr.length; i++) {
		    				if(i == departIdsArr.length - 1){
		    					sql.append("'").append(departIdsArr[i]).append("'");
		    				}else{
		    					sql.append("'").append(departIdsArr[i]).append("',");
		    				}
		    			}
		    			sql.append(") ");
		    		}
		    		if(!esTypes.equals("")){
		    			String[] types = esTypes.split(",");
		    			sql.append(" AND es.type IN(");
		    			for (int i = 0; i < types.length; i++) {
		    				if(i == types.length - 1){
		    					sql.append("'").append(types[i]).append("'");
		    				}else{
		    					sql.append("'").append(types[i]).append("',");
		    				}
		    			}
		    			sql.append(") ");
		    		}
		            switch (exType) {
		                case "1"://零地电压异常
		                	conditionSql.append(" AND re.is_zero_ground_voltage_ex = 1 ");
		                    break;
		                case "2"://湿度过大
		                	conditionSql.append(" AND re.is_humidity_ex = 1 ");
		                    break;
		                case "3"://温度异常
		                	conditionSql.append(" AND re.is_temperature_ex = 1 ");
		                    break;
		                case "4"://冷却液压力异常
		                	conditionSql.append(" AND re.is_coolant_pressure_ex = 1 ");
		                    break;
		                case "5"://冷却液面高度异常
		                	conditionSql.append(" AND re.is_coolant_height_ex = 1 ");
		                    break;
		                case "6"://亮度低
		                	conditionSql.append(" AND re.is_brightness_ex = 1 ");
		                    break;
		                case "7"://风速异常
		                	conditionSql.append(" AND re.is_wind_speed_ex = 1 ");
		                    break;
		                case "8"://参加培训人数不足
		                	conditionSql.append(" AND re.is_training_num_ex = 1 ");
		                	break;
		            }
		        } else if (StringUtil.isNotEmpty(ttId)) {//如果省份Id有值，则是在 区域汇总里点进来的
		        	conditionSql.append(" AND ptt.id = '").append(ttId).append("' ");
		        }
			}else if(StringUtil.isNotEmpty(ipEndTimeYear)){
				conditionSql.append("    AND YEAR (ip.report_commit_time) = '").append(ipEndTimeYear).append("' ");
				conditionSql.append("    AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
				if(StringUtil.isNotEmpty(ipEndTimeMonth)){
					conditionSql.append(" AND MONTH (ip.report_commit_time) = '").append(ipEndTimeMonth).append("' ");
				}
			}
		        
			int totalCount = 0;
			Integer page = 1,rows = 10;
			Map<String,String[]> paramsMap = request.getParameterMap();
			rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
			page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
			if(StringUtil.isNotEmpty(paramsMap.get("planId")) && StringUtil.isNotEmpty(paramsMap.get("planId")[0])){
				conditionSql.append(" AND ip.plan_id LIKE '%").append(paramsMap.get("planId")[0].toString()).append("%' ");
			}
			if(StringUtil.isNotEmpty(paramsMap.get("esSn")) && StringUtil.isNotEmpty(paramsMap.get("esSn")[0])){
				conditionSql.append(" AND es.sn LIKE '%").append(paramsMap.get("esSn")[0].toString()).append("%' ");
			}
			if(StringUtil.isNotEmpty(paramsMap.get("cmName")) && StringUtil.isNotEmpty(paramsMap.get("cmName")[0])){
				conditionSql.append(" AND cm.name LIKE '%").append(paramsMap.get("cmName")[0].toString()).append("%' ");
			}
			if(StringUtil.isNotEmpty(paramsMap.get("cmDepartName")) && StringUtil.isNotEmpty(paramsMap.get("cmDepartName")[0])){
				conditionSql.append(" AND dp.departname LIKE '%").append(paramsMap.get("cmDepartName")[0].toString()).append("%' ");
			}
			if(StringUtil.isNotEmpty(paramsMap.get("es.territoryId.TSTerritory.territoryName")) && StringUtil.isNotEmpty(paramsMap.get("es.territoryId.TSTerritory.territoryName")[0])){
				conditionSql.append(" AND ptt.territoryname = '").append(paramsMap.get("es.territoryId.TSTerritory.territoryName")[0].toString()).append("' ");
			}
			if(StringUtil.isNotEmpty(paramsMap.get("es.territoryId")) && StringUtil.isNotEmpty(paramsMap.get("es.territoryId")[0])){
				conditionSql.append(" AND tt.territoryname = '").append(paramsMap.get("es.territoryId")[0].toString()).append("' ");
			}
			if(StringUtil.isNotEmpty(paramsMap.get("ipDepartName")) && StringUtil.isNotEmpty(paramsMap.get("ipDepartName")[0])){
				conditionSql.append(" AND ipDp.departname LIKE '%").append(paramsMap.get("ipDepartName")[0].toString()).append("%' ");
			}
			List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();

			rsMapList = findForJdbc(sql.append(conditionSql.toString()).toString());
			dataGrid.setTotal(rsMapList.size());
			if(rows != 0 || page != 0){
				dataGrid.setPage(page);
				dataGrid.setRows(rows);
				rsMapList = findForJdbc(sql.append(conditionSql.toString()).toString(), page, rows);
			}
			for (Map<String, Object> map : rsMapList) {
				Map<String, Object> jsMap = null;
				String esTypeId = map.get("esTypeId").toString();
				switch (exType) {
					case "1"://零地电压异常
						if(map.get("is_zero_ground_voltage_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_A);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("zero_ground_voltage_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "2"://湿度过大
						if(map.get("is_humidity_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_B);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("humidity_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "3"://温度异常
						if(map.get("is_temperature_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_C);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("temperature_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "4"://冷却液压力异常
						if(map.get("is_coolant_pressure_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_D);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("coolant_pressure_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "5"://冷却液面高度异常
						if(map.get("is_coolant_height_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_E);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("coolant_height_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "6"://亮度低
						if(map.get("is_brightness_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_F);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("brightness_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "7"://风速异常
						if(map.get("is_wind_speed_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_G);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("wind_speed_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					case "8"://参加培训人数不足
						if(map.get("is_training_num_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_H);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("training_num_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						break;
					default:
						if(map.get("is_zero_ground_voltage_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_A);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("zero_ground_voltage_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_humidity_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_B);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("humidity_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_temperature_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_C);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("temperature_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_coolant_pressure_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_D);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("coolant_pressure_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_coolant_height_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_E);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("coolant_height_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_brightness_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_F);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("brightness_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_wind_speed_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_G);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("wind_speed_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
						if(map.get("is_training_num_ex").toString().equals("1")){
							jsMap = new HashMap<String, Object>();
							jsMap.put("exType", StaticValue.DIC_IR_EX_TYPE_H);
							jsMap.put("esRuleValue", esTypeRules.get(esTypeId + jsMap.get("exType")));
							jsMap.put("exValue", map.get("training_num_ex_val").toString());
							jsMap.putAll(map);
							datagridList.add(jsMap);
						}
				}
			}

//			sql = new StringBuilder();
//			sql.append(" SELECT ");
//			switch (exType) {
//	            case "1"://零地电压异常
//	            	sql.append("     SUM(IFNULL(is_zero_ground_voltage_ex,0)) AS total ");
//	                break;
//	            case "2"://湿度过大
//	            	sql.append("     SUM(IFNULL(is_humidity_ex,0)) AS total ");
//	                break;
//	            case "3"://温度异常
//	    			sql.append("     SUM(IFNULL(is_temperature_ex,0)) AS total ");
//	                break;
//	            case "4"://冷却液压力异常
//	    			sql.append("     SUM(IFNULL(is_coolant_pressure_ex,0)) AS total ");
//	                break;
//	            case "5"://冷却液面高度异常
//	    			sql.append("     SUM(IFNULL(is_coolant_height_ex,0)) AS total ");
//	                break;
//	            case "6"://亮度低
//	    			sql.append("     SUM(IFNULL(is_brightness_ex,0)) AS total ");
//	                break;
//	            case "7"://风速异常
//	    			sql.append("     SUM(IFNULL(is_wind_speed_ex,0)) AS total ");
//	                break;
//	            case "8"://参加培训人数不足
//	    			sql.append("     SUM(IFNULL(is_training_num_ex,0)) AS total ");
//	            	break;
//            	default:
//	            	sql.append("     SUM(IFNULL(is_zero_ground_voltage_ex,0)) + ");
//	    			sql.append("     SUM(IFNULL(is_humidity_ex,0)) + ");
//	    			sql.append("     SUM(IFNULL(is_temperature_ex,0)) + ");
//	    			sql.append("     SUM(IFNULL(is_coolant_pressure_ex,0)) + ");
//	    			sql.append("     SUM(IFNULL(is_coolant_height_ex,0)) +  ");
//	    			sql.append("     SUM(IFNULL(is_brightness_ex,0)) + ");
//	    			sql.append("     SUM(IFNULL(is_wind_speed_ex,0)) + ");
//	    			sql.append("     SUM(IFNULL(is_training_num_ex,0)) AS total ");
//	        }
//			
//			sql.append(" FROM ");
//			sql.append("     zy_report_exceptions re ");
//			sql.append(joinSql.toString());
//			sql.append(" WHERE 1=1 ");
//			sql.append(conditionSql.toString());
//			Map<String, Object> countMap = super.findOneForJdbc(sql.toString());
//			if(null != countMap && countMap.size() > 0){
//				totalCount = Integer.valueOf(countMap.get("total").toString());
//			}
			dataGrid.setResults(datagridList);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * 返件报告数据
	 * 2018-6-28 上午15:00:55
	 * */
	@Override
	public List<Map<String, Object>> findReturnReportData() {
 		String sql = "SELECT\n" +
				"\tre.id,re.code,\n" +
				"\tCASE\n" +
				"WHEN re. STATUS = 1 THEN\n" +
				"\t'未返件'\n" +
				"WHEN re. STATUS = 2 THEN\n" +
				"\t'物流待处理'\n" +
				"WHEN re. STATUS = 3 THEN\n" +
				"\t'取件中'\n" +
				"WHEN re. STATUS = 4 THEN\n" +
				"\t'已入库'\n" +
				"WHEN re. STATUS = 5 THEN\n" +
				"\t'已取消'\n" +
				"END AS STATUS,\n" +
				" CASE\n" +
				"WHEN d.deliver_type = 1 THEN\n" +
				"\t'备件申请'\n" +
				"WHEN d.deliver_type = 2 THEN\n" +
				"\t'IMAX'\n" +
				"WHEN d.deliver_type = 3 THEN\n" +
				"\t'借用'\n" +
				"WHEN d.deliver_type = 4 THEN\n" +
				"\t'零件采购'\n" +
				"WHEN d.deliver_type = 5 THEN\n" +
				"\t'灯索赔'\n" +
				"WHEN d.deliver_type = 6 THEN\n" +
				"\t'备件新损'\n" +
				"WHEN d.deliver_type = 7 THEN\n" +
				"\t'其他'\n" +
				"WHEN d.deliver_type = 8 THEN\n" +
				"\t'备件销售'\n" +
				"END AS deliver_type,\n" +
				" ifnull(re.logistics, '') AS logistics,\n" +
				" ifnull(re.so_code, '') AS so_code,\n" +
				" ifnull(re.sr_code, '') AS sr_code,\n" +
				" ifnull(red.item, '') AS item,\n" +
				" ifnull(bed.matter_num, '') AS matter_num,\n" +
				" ifnull(red.sn, '') AS sn,\n" +
				" ifnull(bed.ver_code, '') AS ver_code,\n" +
				" ifnull(red.item_total, '') AS item_total,\n" +
				" ifnull(s.sn, '') AS serviceSn,\n" +
				" CASE\n" +
				"WHEN re.equipment_apply_id IS NULL THEN\n" +
				"\t'CSA'\n" +
				"ELSE\n" +
				"\t(\n" +
				"\t\tSELECT\n" +
				"\t\t\tdp.shortname\n" +
				"\t\tFROM\n" +
				"\t\t\tt_s_base_user bu\n" +
				"\t\tLEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID\n" +
				"\t\tLEFT JOIN t_s_depart dp ON dp.ID = uo.org_id\n" +
				"\t\tWHERE\n" +
				"\t\t\tbu.id IN (\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tu.id\n" +
				"\t\t\t\tFROM\n" +
				"\t\t\t\t\tt_s_role r\n" +
				"\t\t\t\tLEFT JOIN t_s_role_user ru ON r.id = ru.roleid\n" +
				"\t\t\t\tLEFT JOIN t_s_user u ON ru.userid = u.id\n" +
				"\t\t\t\tWHERE\n" +
				"\t\t\t\t\tu.id IN (\n" +
				"\t\t\t\t\t\tSELECT\n" +
				"\t\t\t\t\t\t\tid\n" +
				"\t\t\t\t\t\tFROM\n" +
				"\t\t\t\t\t\t\tt_s_base_user\n" +
				"\t\t\t\t\t\tWHERE\n" +
				"\t\t\t\t\t\t\tusername = ea.create_by\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t)\n" +
				"\t)\n" +
				"END AS 'applyThird',\n" +
				" ifnull(bed.warehouse_position, '') AS warehouse_position,\n" +
				" ifnull(w. NAME, '') AS NAME,\n" +
				" ifnull(tst1.territoryname, '') AS sheng,\n" +
				" ifnull(tst2.territoryname, '') AS shi,\n" +
				" ifnull(d.logistics_sendtime, '') AS logistics_sendtime,\n" +
				" ifnull(d.gettime, '') AS gettime,\n" +
				" CASE\n" +
				"WHEN d.shi = w.shi THEN\n" +
				"\t(\n" +
				"\t\tifnull(\n" +
				"\t\t\t(\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tdate_add(\n" +
				"\t\t\t\t\t\tnoticeReturn_time,\n" +
				"\t\t\t\t\t\tINTERVAL 3 DAY\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t),\n" +
				"\t\t\t''\n" +
				"\t\t)\n" +
				"\t)\n" +
				"ELSE\n" +
				"\t(\n" +
				"\t\tifnull(\n" +
				"\t\t\t(\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tdate_add(\n" +
				"\t\t\t\t\t\tnoticeReturn_time,\n" +
				"\t\t\t\t\t\tINTERVAL 7 DAY\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t),\n" +
				"\t\t\t''\n" +
				"\t\t)\n" +
				"\t)\n" +
				"END AS 'returnTime',\n" +
				" ifnull(re.backwarehouse_time, '') AS backwarehouse_time,\n" +
				" ifnull(re.INWAREHOUSE_TIME, '') AS INWAREHOUSE_TIME,\n" +
				" CASE\n" +
				"WHEN re.INWAREHOUSE_TIME <= re.backwarehouse_time THEN\n" +
				"\t'Y'\n" +
				"ELSE\n" +
				"\t'N'\n" +
				"END AS performance,\n" +
				" ifnull(re.unqualified, '') AS unqualified,\n" +
				" ifnull(s.judge400, '') AS judge400\n" +
				"FROM\n" +
				"\tcbs_return_equipment re\n" +
				"LEFT JOIN cbs_deliver d ON re.deliver_code = d. CODE\n" +
				"LEFT JOIN cbs_return_equipment_detail red ON re.id = red.return_equipment_id\n" +
				"LEFT JOIN cbs_back_equipment_detail bed ON bed.return_equipment_detail_id = red.id\n" +
				"LEFT JOIN cbs_service s ON s. CODE = re.cr_code\n" +
				"LEFT JOIN cbs_equipment_apply ea ON ea.id = re.equipment_apply_id\n" +
				"LEFT JOIN cbs_warehouse w ON w.id = bed.warehouse_id\n" +
				"LEFT JOIN t_s_territory tst1 ON d.sheng = tst1.id\n" +
				"LEFT JOIN t_s_territory tst2 ON d.shi = tst2.id";
		List<Map<String, Object>> forJdbc = systemService.findForJdbc(sql);
		return forJdbc;
	}

	@Override
	public List<Map<String, Object>> findReturnReportDataFilter(String returnTime1,String returnTime2,Integer week,Integer month,String status) {
		String sql = "SELECT\n" +
				"\tre.id,re.code,\n" +
				"\tCASE\n" +
				"WHEN re. STATUS = 1 THEN\n" +
				"\t'未返件'\n" +
				"WHEN re. STATUS = 2 THEN\n" +
				"\t'物流待处理'\n" +
				"WHEN re. STATUS = 3 THEN\n" +
				"\t'取件中'\n" +
				"WHEN re. STATUS = 4 THEN\n" +
				"\t'已入库'\n" +
				"WHEN re. STATUS = 5 THEN\n" +
				"\t'已取消'\n" +
				"END AS STATUS,\n" +
				" CASE\n" +
				"WHEN d.deliver_type = 1 THEN\n" +
				"\t'备件申请'\n" +
				"WHEN d.deliver_type = 2 THEN\n" +
				"\t'IMAX'\n" +
				"WHEN d.deliver_type = 3 THEN\n" +
				"\t'借用'\n" +
				"WHEN d.deliver_type = 4 THEN\n" +
				"\t'零件采购'\n" +
				"WHEN d.deliver_type = 5 THEN\n" +
				"\t'灯索赔'\n" +
				"WHEN d.deliver_type = 6 THEN\n" +
				"\t'备件新损'\n" +
				"WHEN d.deliver_type = 7 THEN\n" +
				"\t'其他'\n" +
				"WHEN d.deliver_type = 8 THEN\n" +
				"\t'备件销售'\n" +
				"END AS deliver_type,\n" +
				" ifnull(re.logistics, '') AS logistics,\n" +
				" ifnull(re.so_code, '') AS so_code,\n" +
				" ifnull(re.sr_code, '') AS sr_code,\n" +
				" ifnull(red.item, '') AS item,\n" +
				" ifnull(bed.matter_num, '') AS matter_num,\n" +
				" ifnull(red.sn, '') AS sn,\n" +
				" ifnull(bed.ver_code, '') AS ver_code,\n" +
				" ifnull(red.item_total, '') AS item_total,\n" +
				" ifnull(s.sn, '') AS serviceSn,\n" +
				" CASE\n" +
				"WHEN re.equipment_apply_id IS NULL THEN\n" +
				"\t'CSA'\n" +
				"ELSE\n" +
				"\t(\n" +
				"\t\tSELECT\n" +
				"\t\t\tdp.shortname\n" +
				"\t\tFROM\n" +
				"\t\t\tt_s_base_user bu\n" +
				"\t\tLEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID\n" +
				"\t\tLEFT JOIN t_s_depart dp ON dp.ID = uo.org_id\n" +
				"\t\tWHERE\n" +
				"\t\t\tbu.id IN (\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tu.id\n" +
				"\t\t\t\tFROM\n" +
				"\t\t\t\t\tt_s_role r\n" +
				"\t\t\t\tLEFT JOIN t_s_role_user ru ON r.id = ru.roleid\n" +
				"\t\t\t\tLEFT JOIN t_s_user u ON ru.userid = u.id\n" +
				"\t\t\t\tWHERE\n" +
				"\t\t\t\t\tu.id IN (\n" +
				"\t\t\t\t\t\tSELECT\n" +
				"\t\t\t\t\t\t\tid\n" +
				"\t\t\t\t\t\tFROM\n" +
				"\t\t\t\t\t\t\tt_s_base_user\n" +
				"\t\t\t\t\t\tWHERE\n" +
				"\t\t\t\t\t\t\tusername = ea.create_by\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t)\n" +
				"\t)\n" +
				"END AS 'applyThird',\n" +
				" ifnull(bed.warehouse_position, '') AS warehouse_position,\n" +
				" ifnull(w. NAME, '') AS NAME,\n" +
				" ifnull(tst1.territoryname, '') AS sheng,\n" +
				" ifnull(tst2.territoryname, '') AS shi,\n" +
				" ifnull(d.logistics_sendtime, '') AS logistics_sendtime,\n" +
				" ifnull(d.gettime, '') AS gettime,\n" +
				" CASE\n" +
				"WHEN d.shi = w.shi THEN\n" +
				"\t(\n" +
				"\t\tifnull(\n" +
				"\t\t\t(\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tdate_add(\n" +
				"\t\t\t\t\t\tnoticeReturn_time,\n" +
				"\t\t\t\t\t\tINTERVAL 3 DAY\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t),\n" +
				"\t\t\t''\n" +
				"\t\t)\n" +
				"\t)\n" +
				"ELSE\n" +
				"\t(\n" +
				"\t\tifnull(\n" +
				"\t\t\t(\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tdate_add(\n" +
				"\t\t\t\t\t\tnoticeReturn_time,\n" +
				"\t\t\t\t\t\tINTERVAL 7 DAY\n" +
				"\t\t\t\t\t)\n" +
				"\t\t\t),\n" +
				"\t\t\t''\n" +
				"\t\t)\n" +
				"\t)\n" +
				"END AS 'returnTime',\n" +
				" ifnull(re.backwarehouse_time, '') AS backwarehouse_time,\n" +
				" ifnull(re.INWAREHOUSE_TIME, '') AS INWAREHOUSE_TIME,\n" +
				" CASE\n" +
				"WHEN re.INWAREHOUSE_TIME <= re.backwarehouse_time THEN\n" +
				"\t'Y'\n" +
				"ELSE\n" +
				"\t'N'\n" +
				"END AS performance,\n" +
				" ifnull(re.unqualified, '') AS unqualified,\n" +
				" ifnull(s.judge400, '') AS judge400,\n" +
				" ifnull(re.noticeReturn_time, '') AS noticeReturn_time\n" +
				"FROM\n" +
				"\tcbs_return_equipment re\n" +
				"LEFT JOIN cbs_deliver d ON re.deliver_code = d. CODE\n" +
				"LEFT JOIN cbs_return_equipment_detail red ON re.id = red.return_equipment_id\n" +
				"LEFT JOIN cbs_back_equipment_detail bed ON bed.return_equipment_detail_id = red.id\n" +
				"LEFT JOIN cbs_service s ON s. CODE = re.cr_code\n" +
				"LEFT JOIN cbs_equipment_apply ea ON ea.id = re.equipment_apply_id\n" +
				"LEFT JOIN cbs_warehouse w ON w.id = bed.warehouse_id\n" +
				"LEFT JOIN t_s_territory tst1 ON d.sheng = tst1.id\n" +
				"LEFT JOIN t_s_territory tst2 ON d.shi = tst2.id" +
				" where 1=1";
				if(StringUtil.isNotEmpty(returnTime1)){
					sql += " and re.noticeReturn_time >= '" + returnTime1 + "'";
				}
				if(StringUtil.isNotEmpty(returnTime2)){
					sql += " and re.noticeReturn_time <= '" + returnTime2 + "'";
				}
				if(week !=0){
					sql += " and WEEK(date_add(re.create_date,interval 6 day),2)  = '" + week + "'";
				}
				if(month !=0){
					String m = "";
					if(month<10){
						m = month.toString();
						m = "0"+m;
					}else {
						m = month.toString();
					}
					sql += " and date_format(re.create_date,'%m')='" + m + "'";
				}
				if(StringUtil.isNotEmpty(status)){
					sql += " and re.status = '" + status + "'";
				}
		List<Map<String, Object>> forJdbc = systemService.findForJdbc(sql);
		return forJdbc;
	}
	
	/*public void queryEsTypesForIrExReport(DataGrid dataGrid, HttpServletRequest request){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" et.id, et.type_desc AS typeDesc ");
		sql.append(" FROM ");
		sql.append(" zy_equipment_type et ");
		sql.append(" LEFT JOIN zy_equipment_type et1 ON et.parent_id = et1.id ");
		sql.append(" LEFT JOIN zy_equipment_type et2 ON et1.parent_id = et2.id ");
		sql.append(" WHERE ");
		sql.append(" et2.type_desc = '放映机' ");
		Map<String,String[]> paramsMap = request.getParameterMap();
		if(StringUtil.isNotEmpty(paramsMap.get("typeDesc")) && StringUtil.isNotEmpty(paramsMap.get("typeDesc")[0])){
			sql.append(" AND et.type_desc LIKE '%").append(paramsMap.get("typeDesc")[0].toString()).append("%' ");
		}
		int totalCount = 0;
		Integer page = 1,rows = 10;
		rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
		page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
		List<Map<String, Object>> rsMapList = new ArrayList<Map<String,Object>>();
		if(rows == 0 && page == 0){
			rsMapList = findForJdbc(sql.toString());
		}else{
			dataGrid.setPage(page);
			dataGrid.setRows(rows);
			rsMapList = findForJdbc(sql.toString(), page, rows);
		}
		List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
		if(null != countMapList && countMapList.size() > 0){
			totalCount = countMapList.size();
		}
		dataGrid.setTotal(totalCount);
		dataGrid.setResults(rsMapList);
	}*/

	
	
	
	
}